OpenEdge Development:<br />Progress Dynamics Basic Development Determining the proper batch size

The number of rows read at a time is a configurable instance property of the SDO called RowsToBatch. This notion of batching rows is important because it can be very time-consuming to read all of the records that satisfy the SDO’s query definition into the RowObject table at once, before any of them are viewed by the client SmartObjects.

What follows are some things to keep in mind when setting the RowsToBatch property (whose initial value, somewhat arbitrarily, is 50).

It is highly advisable to limit the number of rows that satisfy the database query to the smallest number possible. Although users might think that they want to browser through large numbers of database rows, there is almost never a need to do this. Use of the filter button on an object controller-style browse window, or using the SmartFilter object, can direct the user more efficiently to the exact record or subset of records really needed.

Remember that you should usually define SDOs with the most general possible WHERE clause (typically no WHERE clause at all beyond what might be needed to describe a join, if there is one). You can then use this one SDO in many situations where the data set is restricted by a WHERE clause defined at run time to present to the user only those rows that really need to be seen. If the result set of the query is small, it is usually best to set the batch size large enough so that all the rows can be retrieved at once. (To put this another way, it is probably a good idea to restrict the result set size wherever possible so that the 50 row default is more than adequate to retrieve all of it at once.)

Also, keep in mind that if a user applies a filter to a data set using the Progress Dynamics filter button, that filter can be saved permanently in the Repository so that the query will be filtered the same way the next time the user starts that part of the application. Encouraging the user to choose the Filter button can help cut down on unnecessary traffic between client and server.

There are several advantages to getting the entire (possibly filtered) result set at once. First, if there is a browser browsing the RowObject query, it will have certain unavoidable quirks if the result set is retrieved in multiple batches. If the user scrolls to the end of the first batch by clicking and holding the down arrow on the vertical scrollbar, he must release the mouse from the scrollbar before the OFF-END trigger can fire. This trigger retrieves another batch of rows from the server, adds them to the client-side temp-table query, and reopens and repositions the query to allow the user to continue scrolling. Also, the thumb size of the scrollbar will not reliably reflect the total number of rows in the result set when it not retrieved all at once.

Another factor is that it is easy and frequently useful to re-sort or further filter the RowObject query after it has been retrieved. This clearly makes sense only after the entire result set has been retrieved. Sorting a Progress Dynamics browser by clicking on a column header, for example, will only sort the records already retrieved from the database. This will not be very useful if that is not the whole data set.

However, there are many cases where it is necessary to allow a large data set to be browsed (or if your users simply insist on doing this). RowsToBatch lets you determine how many rows at a time to get. The larger the batch size, the longer it takes for the application screen to come up; the smaller the batch size, the more frequent the interruptions when the user scrolls from one batch to another. You must balance these two considerations. One compromise that can be effective in many cases is to set the initial batch size to be very small (for example, just enough rows to fill the view port, if there is a browser). Then programmatically reset it to a much larger number if the user wants to see other rows. This minimizes waiting time before the application window appears. The appropriate place to do this is in a local initializeObject procedure either in the logic procedure for the SDO itself (if this behavior should occur wherever the SDO is used) or in a local initializeObject procedure in a custom super procedure for the dynamic window where the SDO is used.

In the following example, the custom code explicitly sets RowsToBatch to a small number before the standard initialization code is executed. (It overrides any other setting of the property, either to its default value or some other value). The code then resets the value to a larger number after the initial batch has been retrieved. This code would go into the logic procedure (which is the custom super procedure) for the SDO. Alternatively, your code could simply allow the instance property setting to be used initially, and then reset the property value after the RUN SUPER statement, as shown:

/------------------------------------------------------------------- Purpose: Local Override of initializeObject in an SDO, to reset the RowsToBatch property following the initialization of the SDO, so that up to 2000 additional rows will be retrieved if the user scrolls out of the initial batch. Parameters: none --------------------------------------------------------------------/ /* Code placed here will execute BEFORE standard behavior. / DYNAMIC-FUNCTION('setRowsToBatch':U IN TARGET-PROCEDURE, INPUT 20). RUN SUPER. / Code placed here will execute AFTER standard behavior. */ DYNAMIC-FUNCTION('setRowsToBatch':U IN TARGET-PROCEDURE, INPUT 2000). END PROCEDURE

/*------------------------------------------------------------------- 
 Purpose:   Local Override of initializeObject in an SDO, 
        to reset the RowsToBatch property following the 
        initialization of the SDO, so that 
        up to 2000 additional rows will be retrieved if the  
        user scrolls out of the initial batch. 
 Parameters: none 
--------------------------------------------------------------------*/ 
 /* Code placed here will execute BEFORE standard behavior.  */ 
  DYNAMIC-FUNCTION('setRowsToBatch':U IN TARGET-PROCEDURE, 
   INPUT 20). 
  RUN SUPER. 
 /* Code placed here will execute AFTER standard behavior.  */ 
  DYNAMIC-FUNCTION('setRowsToBatch':U IN TARGET-PROCEDURE, 
   INPUT 2000). 
END PROCEDURE

Another property to consider where larger result sets are concerned is RebuildOnRepos. This logical SmartDataObject property is false by default. If the user chooses the Last button on a Navigation Panel or otherwise repositions to some row not currently in the client-side RowObject table, the SDO will retrieve one batch of rows after another until the desired row is reached. This can take a very long time if the result set is large. If this is the case you should check on the Rebuild On Reposition toggle box in the design time instance Property dialog box for the SDO to reset it to true.

In this case, when the user repositions to a row outside the current client-side data set, the client-side temp-table will be thrown away, the database query will be repositioned directly to the desired row, and the RowObject temp-table will be rebuilt backwards or forwards from that new starting point. This makes doing a Last much faster, but it can force the SDO to retrieve the same rows multiple times if the user alternates back and forth between the first and last rows, for example. A good rule of thumb here is to set RebuildOnRepos to true if the number of rows in the result set is likely to be much larger than the batch size. Otherwise leave it false, so that the overhead of getting all the rows into the client temp-table happens only once.